﻿CREATE function [dbo].[GetAvg1](@DocsDate datetime, @DeptId int, @ContragentId INT)
returns decimal(19,2)
as
begin
declare @result decimal(19,2)
declare @tab table(PartyId int primary key clustered, summ int, Price decimal(19,2))
IF (@ContragentId IS NULL)
BEGIN
insert into @tab(PartyId, summ,Price)
SELECT     g.PartyId, SUM(g.Qntty) AS Sum,p.RetailPrice AS Price
FROM         CA.GoodsDocLines AS g INNER JOIN
CA.Documents AS d ON g.DocId = d.Id
INNER JOIN CD.Parties AS p ON g.PartyId = p.Id
WHERE     (g.DepartmentId = @DeptId) AND (d.DocDate > @DocsDate) AND (d.DocDate < DATEADD(dd,1, @DocsDate))
GROUP BY g.PartyId,p.RetailPrice
HAVING      (SUM(-g.Qntty) > 0)
ORDER BY g.PartyId
END
ELSE
BEGIN
insert into @tab(PartyId, summ,Price)
SELECT     g.PartyId, SUM(g.Qntty) AS Sum, i.SupplierPrice AS Price
FROM         CA.GoodsDocLines AS g INNER JOIN
CA.Documents AS d ON g.DocId = d.Id
INNER JOIN CD.Parties AS p ON g.PartyId = p.Id
INNER JOIN CD.InputLines AS i ON i.Id = p.InputLineId AND i.ContragentId = @ContragentId
WHERE     (g.DepartmentId = @DeptId) AND (d.DocDate > @DocsDate) AND 
(d.DocDate < DATEADD(dd,1, @DocsDate))
GROUP BY g.PartyId, i.SupplierPrice
HAVING      (SUM(-g.Qntty) > 0)
ORDER BY g.PartyId

END

select @result = sum(price)/count(PartyId) from @tab
return @result
end